﻿Imports Abyx.SVCMS.Library
Imports System.IO
Imports Abyx.SVCMS.Library.DB
Imports System.Windows.Forms
Imports System.Data.SqlClient

Public Class AppPreLoader

    Dim Status_Label As Label = Nothing

    Public Sub New()
        Me.Status_Label = Nothing
    End Sub

    Public Sub New(Status_Label As Label)
        If Me.Status_Label IsNot Nothing Then Me.Status_Label = Status_Label
    End Sub

    'Do application preloading tasks
    Public Sub DoPreLoadingTasks()

        Threading.Thread.Sleep(1000)

        'Configure database updates
        ConfigureDatabaseUpdates()

        Threading.Thread.Sleep(1000)

        'Check and do license update
        CheckLicenseInformation()

        Threading.Thread.Sleep(1000)


    End Sub

    'Updates checking and loading
    Private Sub ConfigureDatabaseUpdates()

        PutStatusMessage("Checking for updates...please wait")

        'Check updates folder and create if it does not exist
        Dim updatesFolder As String = Application.StartupPath
        If updatesFolder.EndsWith("\") = False Then updatesFolder &= "\"
        updatesFolder &= "Updates_Pending\"
        If My.Computer.FileSystem.DirectoryExists(updatesFolder) = False Then
            My.Computer.FileSystem.CreateDirectory(updatesFolder)
        End If

        'Fetch script files from updates folder
        Dim updatesDirectory As DirectoryInfo = My.Computer.FileSystem.GetDirectoryInfo(updatesFolder)
        Dim updateFiles() As FileInfo = updatesDirectory.GetFiles("*.sql")

        'Check for non loaded updates and load them
        Dim nonLoadedUpdatesFound As Boolean = False
        If (Not updateFiles Is Nothing) AndAlso (updateFiles.Count > 0) Then
            'Get current DB version number
            Dim verConfig As ConfigType = SysManager.Config_Get("System", "DBVersion")
            Dim ver As Integer = verConfig.ConfigValue
            'Iterate updates loading them
            Do
                'next ver
                ver += 1
                'check if update exists
                Dim cuFile As String = updatesFolder & SysManager.FileNameFormatSqlUpdates(ver)
                Dim upFiles As IEnumerable(Of FileInfo) = From supFile As FileInfo In updateFiles _
                                                          Where supFile.FullName = cuFile _
                                                          Select supFile
                Dim finalLoadFile As FileInfo = My.Computer.FileSystem.GetFileInfo(cuFile)
                If (IsNothing(finalLoadFile)) _
                    OrElse (My.Computer.FileSystem.FileExists(finalLoadFile.FullName) = False) Then
                    Exit Do
                Else
                    nonLoadedUpdatesFound = True
                End If
            Loop
        End If


        Dim dbBackupBeforeUpdatesFile As String = ""
        If nonLoadedUpdatesFound = True Then
            dbBackupBeforeUpdatesFile = DoDbBackupToFile()
            If dbBackupBeforeUpdatesFile <> "" Then

                'Check for non loaded updates and load them
                Dim updateLoadErrorsFound As Boolean = False
                If (Not updateFiles Is Nothing) AndAlso (updateFiles.Count > 0) Then
                    'Get current DB version number
                    Dim verConfig As ConfigType = SysManager.Config_Get("System", "DBVersion")
                    Dim ver As Integer = verConfig.ConfigValue
                    'Iterate updates loading them
                    Do
                        'check if update exists
                        Dim cuFile As String = updatesFolder & SysManager.FileNameFormatSqlUpdates(ver)
                        Dim upFiles As IEnumerable(Of FileInfo) = From supFile As FileInfo In updateFiles _
                                                                  Where supFile.FullName = cuFile _
                                                                  Select supFile
                        Dim finalLoadFile As FileInfo = My.Computer.FileSystem.GetFileInfo(cuFile)
                        If (IsNothing(finalLoadFile)) _
                            OrElse (My.Computer.FileSystem.FileExists(finalLoadFile.FullName) = False) Then
                            Exit Do
                        Else
                            Try
                                PutStatusMessage("Loading update file " & finalLoadFile.Name)

                                'Process the script
                                RunSQLServerScript(finalLoadFile, ver)

                                PutStatusMessage("Loading update file " & finalLoadFile.Name & " completed")

                            Catch ex As Exception
                                MsgBox("Error loading update file " & finalLoadFile.Name & vbNewLine & _
                                       "Updater will now try to restore the database to previous state..." & vbNewLine & _
                                       ex.ToString, MsgBoxStyle.Critical)
                                updateLoadErrorsFound = True
                                Exit Do
                            End Try
                        End If
                        'next ver
                        ver += 1
                    Loop
                End If

                'try to restore if errors were there
                If updateLoadErrorsFound Then
                    Try
                        DoDbRestoreFromFile(dbBackupBeforeUpdatesFile)
                    Catch ex As Exception
                        MsgBox("Error restoring database with file " & dbBackupBeforeUpdatesFile & vbNewLine & _
                               ex.ToString, MsgBoxStyle.Critical)
                    End Try
                End If

            End If
        End If

        PutStatusMessage("Checking for updates...Completed")

    End Sub

    'License checks
    Public Sub CheckLicenseInformation()

        PutStatusMessage("Checking license information...please wait")

        'Load license from config
        Dim linfo As LicenseInfoType = SysManager.License_Read


        'Update license hardcopy
        Dim cmd As New SqlCommand("SELECT TOP 1 * FROM [SYS_License]")
        Using con As New SqlConnection(Utility.GetConnectionString)
            Try
                con.Open()
                cmd.Connection = con
                Dim dt As DataTable = Utility.GetList(cmd)
                If ((Not dt Is Nothing) AndAlso (dt.Rows.Count > 0)) Then
                    'Update procedure here
                    Dim license As New LicenseType
                    license.Load(dt.Rows(0))
                    With license
                        .CompanyName = linfo.CompanyName
                        .Address = linfo.Address
                        .Contacts = linfo.Contacts
                        .LogoBanner = linfo.Logo_Banner
                        .LogoReport = linfo.Logo_Report
                        .LMUserId = Nothing
                        .LMDate = DateTime.Now
                        .Connection = con
                        .Update()
                    End With
                Else
                    'Insert procedure here
                    Dim license As New LicenseType
                    With license
                        .LicenseId = Guid.NewGuid
                        .CompanyName = linfo.CompanyName
                        .Address = linfo.Address
                        .Contacts = linfo.Contacts
                        .LogoBanner = linfo.Logo_Banner
                        .LogoReport = linfo.Logo_Report
                        .LMUserId = Nothing
                        .LMDate = DateTime.Now
                        .Connection = con
                        .Insert()
                    End With
                End If
            Catch ex As Exception
                MsgBox(AppConstants.Error_Message_License_Hardcopy_Update_Failed, MsgBoxStyle.Critical)
            Finally
                con.Close()
            End Try
        End Using

        PutStatusMessage("Checking license information...complete")

    End Sub

    Private Function DoDbBackupToFile() As String

        Dim fileName As String = "test"
        'Try
        '    Dim settings As SettingsType = Utility.Settings_Read
        '    fileName = settings.DbSettings.DatabaseBackupPath
        '    If fileName.EndsWith("\") = False Then fileName &= "\"
        '    fileName &= "abyx-svcm-" & Now.ToString("yyyyMMdd") & "-BeforeUpdate.bak"
        '    Using con As New SqlConnection(Utility.GetConnectionString)
        '        con.Open()
        '        Dim cmd As New SqlCommand
        '        cmd.Connection = con
        '        cmd.CommandType = CommandType.Text
        '        cmd.CommandText = "BACKUP DATABASE [abyx_svcm] TO DISK = N'" & fileName & "' WITH INIT,CHECKSUM,STOP_ON_ERROR; " & vbNewLine & _
        '                          "RESTORE VERIFYONLY FROM DISK = N'" & fileName & "';"
        '        cmd.ExecuteNonQuery()
        '        con.Close()
        '    End Using
        'Catch ex As Exception
        '    MsgBox("Error backing up database to file " & fileName & vbNewLine & _
        '                               ex.ToString, MsgBoxStyle.Critical)
        '    fileName = ""
        'End Try
        Return fileName

    End Function

    Private Sub DoDbRestoreFromFile(dbBackupFile As String)

        'Try
        '    Using con As New SqlConnection(Utility.GetConnectionString)
        '        con.Open()
        '        Dim cmd As New SqlCommand
        '        cmd.Connection = con
        '        cmd.CommandType = CommandType.Text
        '        cmd.CommandText = "USE master; " & vbNewLine & _
        '                          "RESTORE DATABASE [abyx_svcm] FROM DISK = N'" & dbBackupFile & "' WITH RECOVERY, REPLACE;"
        '        cmd.ExecuteNonQuery()
        '        con.Close()
        '    End Using
        'Catch ex As Exception
        '    MsgBox("Error restoring database from file " & dbBackupFile & vbNewLine & _
        '                               ex.ToString, MsgBoxStyle.Critical)
        'End Try

    End Sub

    Private Sub RunSQLServerScript(ScriptFile As FileInfo, scriptVersion As Integer)

        'Get the script file text
        Dim sqlScript As String = ScriptFile.OpenText().ReadToEnd

        'Concat version number incrementing code
        sqlScript = sqlScript.Replace("BEGIN TRANSACTION", "")
        sqlScript = sqlScript.Replace("COMMIT", "")
        sqlScript = vbNewLine & "BEGIN TRANSACTION" & vbNewLine & _
                    "GO" & vbNewLine & _
                    sqlScript & vbNewLine & _
                    "UPDATE [SYS_Config]" & vbNewLine & _
                    "SET [ConfigValue] = " & scriptVersion & vbNewLine & _
                    "	,[LMDate] = GETDATE()" & vbNewLine & _
                    "WHERE [ModuleName]='System' AND [ConfigName]='DBVersion'" & vbNewLine & _
                    "GO" & vbNewLine & vbNewLine & _
                    "COMMIT" & vbNewLine & _
                    "GO"

        'Prepare SQL batch commands
        Dim allLines() As String = sqlScript.Split(vbNewLine)
        Dim finalBlocks As New Specialized.StringCollection
        Dim sqlBlock As String = ""
        Dim reachedEndOfBlock As Boolean = True
        For lNum As Integer = 0 To allLines.Count - 1
            Dim curLine As String = allLines(lNum).Trim.ToUpper
            If (curLine <> "") And (curLine <> "GO") Then
                sqlBlock = sqlBlock & allLines(lNum) & ControlChars.CrLf
            End If
            'Determine end of block
            If ((curLine = "GO") Or (lNum = allLines.Count - 1)) Then
                'Add blocks to collection
                finalBlocks.Add(sqlBlock)
                sqlBlock = ""
            End If
        Next

        'Run the batch commands
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.Text
        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            Dim trans As SqlTransaction = con.BeginTransaction

            Try
                For index As Integer = 0 To finalBlocks.Count - 1
                    If finalBlocks.Item(index).Trim <> "" Then
                        cmd.CommandText = finalBlocks.Item(index)
                        cmd.Connection = con
                        cmd.Transaction = trans
                        cmd.ExecuteNonQuery()
                    End If
                Next
                trans.Commit()
            Catch ex As Exception
                trans.Rollback()
                con.Close()
                Throw ex
            Finally

                con.Close()
            End Try

        End Using

    End Sub

    Shared Sub RunSQLServerScript2(ByVal ScriptFile As FileInfo)

        ' Run an SQL Script file in a slightly dirty way but no need to redistribute SMO and a lot of other gunk.  
        ' Known limitations:
        ' - does not deal with multiline /* */ comments, let alone nested comments

        Dim DataConnection As New System.Data.SqlClient.SqlConnection(SysManager.GetConnectionString)
        Dim SQLScriptCommand As System.Data.SqlClient.SqlCommand
        Dim sr As StreamReader = Nothing
        Dim sb As System.Text.StringBuilder = Nothing
        Dim line As String = ""
        Dim NormalizedLine As String = ""



        DataConnection.Open()
        Try
            sr = ScriptFile.OpenText
            sb = New System.Text.StringBuilder()
            Dim lineNum As Integer = 0

            Do
                line = sr.ReadLine()
                NormalizedLine = UCase(Trim(line))
                ' Let's read it until the next GO
                If (NormalizedLine Is Nothing Or NormalizedLine.StartsWith("-- ")) Then
                    'ignore line
                    'Debug.Print("RunSQLServerScript: ignoring line: " & line)
                Else
                    If NormalizedLine = "GO" Then


                        If sb.Length > 0 Then


                            If sb.ToString.Contains("DROP DATABASE") Or sb.ToString.Contains("CREATE DATABASE") Or sb.ToString.Contains("ALTER DATABASE") Then
                                ' Don't run this inside a transaction, otherwise we might get
                                ' ALTER DATABASE statement not allowed within multi-statement transaction
                                Debug.Print("RunSQLServerScript: going to run SQL outside of transaction control:" & Environment.NewLine & sb.ToString)
                                SQLScriptCommand = DataConnection.CreateCommand
                                SQLScriptCommand.Transaction = Nothing

                                SQLScriptCommand.CommandType = Data.CommandType.Text
                                SQLScriptCommand.CommandText = sb.ToString
                                Try
                                    SQLScriptCommand.ExecuteNonQuery()
                                Catch SQLex As System.Data.SqlClient.SqlException
                                    Debug.Print("SQL Exception: " & SQLex.Message & vbCrLf & SQLex.ToString)
                                    Throw New ApplicationException("RunSQLServerScript error: " & SQLex.Message)
                                Catch ex As Exception
                                    Debug.Print("Exception: " & ex.Message & vbCrLf & ex.ToString)
                                    Throw New ApplicationException("RunSQLServerScript error: " & ex.Message)
                                Finally
                                    sb.Clear()
                                End Try


                            Else
                                Debug.Print("RunSQLServerScript: going to run SQL:" & Environment.NewLine & sb.ToString)
                                Dim OurTransaction = DataConnection.BeginTransaction 'weird but works.

                                SQLScriptCommand = DataConnection.CreateCommand
                                SQLScriptCommand.Transaction = OurTransaction
                                SQLScriptCommand.CommandType = Data.CommandType.Text
                                SQLScriptCommand.CommandText = sb.ToString
                                Try
                                    SQLScriptCommand.ExecuteNonQuery()
                                    OurTransaction.Commit()
                                Catch SQLex As System.Data.SqlClient.SqlException
                                    If SQLex.Message.Contains("The transaction is rolled back.") Then
                                        Debug.Print("Ignored SQL Exception: " & SQLex.Message)
                                    Else
                                        If Not (OurTransaction.Connection Is Nothing) Then
                                            OurTransaction.Rollback() 'No half objects left if possible       .
                                        End If
                                        Debug.Print("SQL Exception: " & SQLex.Message & vbCrLf & SQLex.ToString)
                                        Throw New ApplicationException("RunSQLServerScript error: " & SQLex.Message)
                                    End If
                                Catch ex As Exception

                                    If Not (OurTransaction.Connection Is Nothing) Then
                                        OurTransaction.Rollback() 'No half objects left if possible       .
                                    End If
                                    Debug.Print("Exception: " & ex.Message & vbCrLf & ex.ToString)
                                    Throw New ApplicationException("RunSQLServerScript error: " & ex.Message)
                                Finally
                                    sb.Clear()
                                End Try
                            End If
                        End If
                    Else
                        If sb.Length > 0 Then
                            sb.Append(ControlChars.CrLf & line) 'explicitly no environment.newline as we're dealing with windows sql server
                        Else
                            sb.Append(line)
                        End If
                    End If
                End If
            Loop Until line Is Nothing
            ' last lines if no go statement follows them
            If sb.Length > 0 Then
                Debug.Print("RunSQLServerScript: going to run SQL:" & Environment.NewLine & sb.ToString)
                Dim OurTransaction = DataConnection.BeginTransaction 'weird but works.
                SQLScriptCommand = DataConnection.CreateCommand
                SQLScriptCommand.Transaction = OurTransaction
                SQLScriptCommand.CommandType = Data.CommandType.Text
                SQLScriptCommand.CommandText = sb.ToString
                Try
                    SQLScriptCommand.ExecuteNonQuery()
                    OurTransaction.Commit()
                Catch SQLex As System.Data.SqlClient.SqlException
                    If SQLex.Message.Contains("The transaction is rolled back.") Then
                        Debug.Print("Ignored SQL Exception: " & SQLex.Message)
                    Else
                        If Not (OurTransaction.Connection Is Nothing) Then
                            OurTransaction.Rollback() 'No half objects left if possible       .
                        End If
                        Debug.Print("SQL Exception: " & SQLex.Message & vbCrLf & SQLex.ToString)
                        Throw New ApplicationException("RunSQLServerScript error: " & SQLex.Message)
                    End If
                Catch ex As Exception
                    If Not (OurTransaction.Connection Is Nothing) Then
                        OurTransaction.Rollback() 'No half objects left if possible       .
                    End If
                    Debug.Print("Exception: " & ex.Message & vbCrLf & ex.ToString)
                    Throw New ApplicationException("RunSQLServerScript error: " & ex.Message)
                End Try

                sb.Clear()
            End If

        Catch AppExc As ApplicationException
            ' Pass it on to the next victim
            Throw
        Catch ex As Exception
            Throw New ApplicationException("RunSQLServerScript error: " & ex.Message)
        Finally
            If sr IsNot Nothing Then sr.Close()
            Reset()
        End Try
    End Sub

    Delegate Sub PutStatusMessageDelegate(Message As String)
    Private Sub PutStatusMessage(Message As String)

        If Not Me.Status_Label Is Nothing Then
            If Me.Status_Label.InvokeRequired Then
                Dim d As New PutStatusMessageDelegate(AddressOf PutStatusMessage)
                Me.Status_Label.Invoke(d, Message)
            Else
                Me.Status_Label.Text = Message
            End If
        End If

    End Sub

End Class
